package com.hwua.dao.Impl;

import com.hwua.Util.C3P0Util;
import com.hwua.dao.IProCategoryDao;
import com.hwua.entity.ProductCategory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class ProCategoryDaoImpl implements IProCategoryDao {
    private QueryRunner queryRunner=null;

    public ProCategoryDaoImpl() {
        queryRunner=new QueryRunner(C3P0Util.getCpds());
    }

    @Override
    public List<ProductCategory> queryAll() throws SQLException {
       /* String sql="select * from amz_product_category where parent_id=?";*/
        String sql="SELECT DISTINCT(t1.id),t1.`name`,t1.parent_id FROM amz_product_category AS t1 ,amz_product_category AS t2 WHERE t1.id = t2.parent_id";
        return queryRunner.query(sql,new BeanListHandler<ProductCategory>(ProductCategory.class));
    }

    //remember!!!
    @Override
    public int getCount(int id) throws Exception {
        String sql="SELECT count(*) from amz_product_category where parent_id=?";
        Object[] args={id};
       return queryRunner.query(sql,new ScalarHandler<>(),args);
    }

    @Override
    public List<ProductCategory> query() throws Exception {
        String sql="SELECT\n" +
                "DISTINCT(t1.id),\n" +
                "t1.`name`,\n" +
                "t1.parent_id\n" +
                "FROM\n" +
                "amz_product_category AS t1 ,\n" +
                "amz_product_category AS t2\n" +
                "WHERE\n" +
                "t1.id = t2.parent_id" ;
        return  queryRunner.query(sql,new BeanListHandler<ProductCategory>(ProductCategory.class));
    }

    @Override
    public List<ProductCategory> queryByID(int parent_id) throws Exception {
        String sql="select * from amz_product_category where parent_id=?";
        Object[] args={parent_id};
        return queryRunner.query(sql,new BeanListHandler<ProductCategory>(ProductCategory.class),args);
    }

    @Override
    public ProductCategory queryBySelfID(int id) throws Exception {
        String sql="select * from amz_product_category where id=?";
        Object[] args={id};
        return queryRunner.query(sql,new BeanHandler<ProductCategory>(ProductCategory.class),args);
    }
}
